package banco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormataNumero;
import utilidades.FormatarData;
import dominio.Cliente;
import dominio.Tipo_Planilha;
import dominio.Valores;
import fachada.FachadaValores;

public class ValoresDAO implements FachadaValores {

@SuppressWarnings("finally")
public void insertValores(Valores v, Cliente c, Tipo_Planilha tp) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = Banco.getConexaoEXTRATOPAES();
            stmt = conn.prepareStatement("insert into valores (srf, pgfn, parcelamento, juros," +
            		"multa, encargos, mesini, cnpj_cliente, total, id_tipo_planilha) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            stmt.setFloat(1, v.getSrf());
            stmt.setFloat(2, v.getPgfn());
            stmt.setFloat(3, v.getParcelamento());
            stmt.setFloat(4, v.getJuros());
            stmt.setFloat(5, v.getMulta());
            stmt.setFloat(6, v.getEncargos());
            System.out.println("dento do dao: " + v.getTotal());            
            stmt.setString(7, FormatarData.formatar(v.getMesini()));
            stmt.setString(8, c.getCnpj().trim());
            stmt.setFloat(9, v.getTotal());
            stmt.setInt(10, tp.getId());
            stmt.executeUpdate();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        catch (Exception e) {
        	e.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
        }
    }

@SuppressWarnings("finally")
public Collection selectValores(Cliente c, Tipo_Planilha tp, boolean quitada) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Collection<Valores> res = new ArrayList<Valores>();
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from valores where cnpj_cliente = ? and id_tipo_planilha = ? and quitada = ?");
        stmt.setString(1, c.getCnpj().trim());
        stmt.setInt(2, tp.getId());
        stmt.setBoolean(3, quitada);
        rs = stmt.executeQuery();
        while (rs.next()){
            Valores v = new Valores();
            v.setEncargos(rs.getFloat("encargos"));
            v.setJuros(rs.getFloat("juros"));
            v.setMesini(FormatarData.inserirData(rs.getString("mesini")));
            v.setMulta(rs.getFloat("multa"));
            v.setParcelamento(rs.getFloat("parcelamento"));
            v.setPgfn(rs.getFloat("pgfn"));
            v.setSrf(rs.getFloat("srf"));
            v.setTotal(FormataNumero.formatarSaida(rs.getFloat("total")));            
            v.setId(rs.getInt("id"));
            v.setMes_string(rs.getString("mesini"));
            v.setPlanilha(rs.getInt("id_tipo_planilha"));
            v.setQuitada(rs.getBoolean("quitada"));
            res.add(v);
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
    }
}

@SuppressWarnings("finally")
public Collection selectQuitadas(boolean quitada) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Collection<Valores> res = new ArrayList<Valores>();
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select srf, pgfn, parcelamento, juros, multa, encargos, mesini, valores.cnpj_cliente, id, total, id_tipo_planilha, " +
        		"quitada, cliente.nome_cliente from valores " +
        		"inner join cliente on (valores.cnpj_cliente = cliente.cnpj_cliente) where quitada = ?");
        stmt.setBoolean(1, quitada);
        rs = stmt.executeQuery();
        while (rs.next()){
            Valores v = new Valores();
            v.setEncargos(rs.getFloat("encargos"));
            v.setJuros(rs.getFloat("juros"));
            v.setMesini(FormatarData.inserirData(rs.getString("mesini")));
            v.setMulta(rs.getFloat("multa"));
            v.setParcelamento(rs.getFloat("parcelamento"));
            v.setPgfn(rs.getFloat("pgfn"));
            v.setSrf(rs.getFloat("srf"));
            v.setTotal(FormataNumero.formatarSaida(rs.getFloat("total")));
            v.setId(rs.getInt("id"));
            v.setMes_string(rs.getString("mesini"));
            v.setPlanilha(rs.getInt("id_tipo_planilha"));
            v.setQuitada(rs.getBoolean("quitada"));
            Cliente c = new Cliente();
            c.setCnpj(rs.getString("cnpj_cliente"));
            c.setNome(rs.getString("nome_cliente"));
            v.setCliente(c);
            res.add(v);
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
    }
}

@SuppressWarnings("finally")
public Valores selectValorId(Float total, String mesini, Tipo_Planilha tp) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Valores v = null;
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from valores where total = ? and mesini = ? and id_tipo_planilha = ?");
        stmt.setFloat(1, total);
        stmt.setString(2, mesini);
        stmt.setInt(3, tp.getId());
        rs = stmt.executeQuery();
        while (rs.next()){
        	v = new Valores();
            v.setEncargos(rs.getFloat("encargos"));
            v.setJuros(rs.getFloat("juros"));
            v.setMesini(FormatarData.inserirData(rs.getString("mesini")));
            v.setMulta(rs.getFloat("multa"));
            v.setParcelamento(rs.getFloat("parcelamento"));
            v.setPgfn(rs.getFloat("pgfn"));
            v.setSrf(rs.getFloat("srf"));
            v.setTotal(FormataNumero.formatarSaida(rs.getFloat("total")));
            v.setId(rs.getInt("id"));
            v.setMes_string(rs.getString("mesini"));
            v.setPlanilha(rs.getInt("id_tipo_planilha"));
            v.setQuitada(rs.getBoolean("quitada"));
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        System.out.println("saiu do select");
        return v;
    }
}

@SuppressWarnings("finally")
public Valores selectValorCliente(int id) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Valores v = new Valores();    
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from valores where id = ?");
        stmt.setInt(1, id);
        rs = stmt.executeQuery();
        while(rs.next()) {
	        v.setEncargos(rs.getFloat("encargos"));
	        v.setJuros(rs.getFloat("juros"));
	        v.setMesini(FormatarData.inserirData(rs.getString("mesini")));
	        v.setMulta(rs.getFloat("multa"));
	        v.setParcelamento(rs.getFloat("parcelamento"));
	        v.setPgfn(rs.getFloat("pgfn"));
	        v.setSrf(rs.getFloat("srf"));
	        v.setId(rs.getInt("id"));
	        v.setTotal(FormataNumero.formatarSaida(rs.getFloat("total")));
	        v.setMes_string(rs.getString("mesini"));
	        v.setPlanilha(rs.getInt("id_tipo_planilha"));
	        v.setQuitada(rs.getBoolean("quitada"));
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return v;
    }
}

	public void updateValores(Valores v, Cliente c) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    System.out.println("ID VALORES: " + v.getId());
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("update valores set srf = ?, pgfn = ?, parcelamento = ?, juros = ?, multa = ?, encargos = ?, total = ?, " +
	        		"mesini = ? where valores.id = ?");
	        stmt.setFloat(1, v.getSrf());
	        stmt.setFloat(2, v.getPgfn());
	        stmt.setFloat(3, v.getParcelamento());
	        stmt.setFloat(4, v.getJuros());
	        stmt.setFloat(5, v.getMulta());
	        stmt.setFloat(6, v.getEncargos());
	        stmt.setFloat(7, v.getTotal());
	        stmt.setString(8, FormatarData.formatar(v.getMesini()));
	        stmt.setInt(9, v.getId());
	        stmt.executeUpdate();
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    
	    catch (Exception e) {
	    	e.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	    }
	}
	
	public void updateValorQuitado(Valores v) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("update valores set quitada = ? where valores.id = ?");
	        stmt.setBoolean(1, v.isQuitada());
	        stmt.setInt(2, v.getId());
	        stmt.executeUpdate();
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    
	    catch (Exception e) {
	    	e.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	    }
	}
}
